from PySide6.QtCore import QThread, Signal
from sqlalchemy.orm import Session


class DatabaseThread(QThread):
    queryResult = Signal(list)
    insertSuccess = Signal()
    updateSuccess = Signal()
    deleteSuccess = Signal()

    def __init__(self, model):
        super().__init__()
        self.model = model

    def run(self):
        session = Session()

        # 查询
        def query():
            result = session.query(self.model).all()
            self.queryResult.emit(result)

        # 插入
        def insert(data):
            new_instance = self.model(**data)
            session.add(new_instance)
            session.commit()
            self.insertSuccess.emit()

        # 更新
        def update(filter_conditions, new_data):
            instances = session.query(self.model).filter(*filter_conditions).all()
            for instance in instances:
                for key, value in new_data.items():
                    setattr(instance, key, value)
            session.commit()
            self.updateSuccess.emit()

        # 删除
        def delete(filter_conditions):
            instances = session.query(self.model).filter(*filter_conditions).all()
            for instance in instances:
                session.delete(instance)
            session.commit()
            self.deleteSuccess.emit()

        session.close()

    def perform_query(self):
        self.start()
        return self.queryResult

    def perform_insert(self, data):
        self.start()

        def insert_from_thread():
            session = Session()
            new_instance = self.model(**data)
            session.add(new_instance)
            session.commit()
            session.close()
            self.insertSuccess.emit()

        self.finished.connect(insert_from_thread)
        return self.insertSuccess

    def perform_update(self, filter_conditions, new_data):
        self.start()

        def update_from_thread():
            session = Session()
            instances = session.query(self.model).filter(*filter_conditions).all()
            for instance in instances:
                for key, value in new_data.items():
                    setattr(instance, key, value)
            session.commit()
            session.close()
            self.updateSuccess.emit()

        self.finished.connect(update_from_thread)
        return self.updateSuccess

    def perform_delete(self, filter_conditions):
        self.start()

        def delete_from_thread():
            session = Session()
            instances = session.query(self.model).filter(*filter_conditions).all()
            for instance in instances:
                session.delete(instance)
            session.commit()
            session.close()
            self.deleteSuccess.emit()

        self.finished.connect(delete_from_thread)
        return self.deleteSuccess


if __name__ == "__main__":
    from PySide6.QtWidgets import QApplication
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    from sqlalchemy import Column, Integer, String
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    engine = create_engine("sqlite:///your_database.db")
    Session = sessionmaker(bind=engine)

    class User(Base):
        __tablename__ = "users"

        id = Column(Integer, primary_key=True)
        username = Column(String, unique=True)
        email = Column(String, unique=True)
        password = Column(String)

        def __repr__(self):
            return f"<User(username='{self.username}', email='{self.email}')>"

    def create_user(username, email, password):
        session = Session()
        new_user = User(username=username, email=email, password=password)
        session.add(new_user)
        session.commit()
        session.close()

    def get_user_by_username(username):
        session = Session()
        user = session.query(User).filter(User.username == username).first()
        session.close()
        return user

    def update_user_email(username, new_email):
        session = Session()
        user = session.query(User).filter(User.username == username).first()
        if user:
            user.email = new_email
            session.commit()
        session.close()

    def delete_user(username):
        session = Session()
        user = session.query(User).filter(User.username == username).first()
        if user:
            session.delete(user)
            session.commit()
        session.close()

    # 创建用户
    create_user("user1", "user1@example.com", "password1")
    create_user("user2", "user2@example.com", "password2")

    # 获取用户
    user = get_user_by_username("user1")
    print(user)

    # 更新用户邮箱
    update_user_email("user1", "newemail@example.com")

    # 再次获取用户查看更新结果
    updated_user = get_user_by_username("user1")
    print(updated_user)

    # 删除用户
    delete_user("user2")

    app = QApplication([])

    thread = DatabaseThread(User)

    # 查询示例
    query_result = thread.perform_query()
    query_result.connect(
        lambda users: [print(user.id, user.name, user.age) for user in users]
    )

    # 插入示例
    data_to_insert = {"name": "NewUser", "age": 28}
    insert_result = thread.perform_insert(data_to_insert)
    insert_result.connect(lambda: print("Insertion successful"))

    # 更新示例
    __filter_conditions = [User.name == "NewUser"]
    new_data = {"age": 30}
    update_result = thread.perform_update(__filter_conditions, new_data)
    update_result.connect(lambda: print("Update successful"))

    # 删除示例
    delete_filter = [User.name == "NewUser"]
    delete_result = thread.perform_delete(delete_filter)
    delete_result.connect(lambda: print("Deletion successful"))

    app.exec()
